<?php
/**
 * Created by muniao.
 * User: muniao
 * Date: 2018/5/31
 * Time: 15:20
 */

namespace app\api\controller\android\v11;


use think\Controller;
use think\Request;
use app\api\model\OrderModel;
use think\Db;
use app\api\model\Authority;
use think\Exception;

class WorkBench extends Controller
{
    protected $us;

    public function _initialize()
    {
        $this->us = Authority::check(1);

    }

    public function index()
    {
        //本月
        $BeginDate = date('Y-m-01', strtotime(date("Y-m-d", time())));
        $starttime = strtotime($BeginDate);
        $enttime   = strtotime(date('Y-m-d', strtotime("$BeginDate +1 month -1 day")));
        //本日
        $beginToday = mktime(0, 0, 0, date('m'), date('d'), date('Y'));
        $endToday   = mktime(0, 0, 0, date('m'), date('d') + 1, date('Y')) - 1;
        //带上门
        $list['door'] = db('workbench_read')
            ->where('user_id', $this->us['user_id'])
            ->where('already', 1)
            ->whereBetween('time', [$beginToday, $endToday])
            ->count();

        //待处理
        $list['communicate'] = db('through')
            ->Join('order', 'order.order_id=through.order_ids', 'left')
            ->where('role', 2)
            ->whereBetween('end_time', [$beginToday, $endToday])
            ->where('admin_id', $this->us['user_id'])
            ->whereNull('through.handle')
            ->group('through.order_ids')
            ->count();

        //审批提醒
        $list['approvalReminder'] = db('approval_info', config('database.zong'))
            ->where('user_id', $this->us['user_id'])
            ->where('user_type', 1)
            ->where('read_time', 0)
            ->where('approval_status', 2)
            ->count();

        //应收款总计
        $list['receivable'] = db('order', config('database.zong'))
            ->Join('order_aggregate', 'order_aggregate.order_id=order.order_id', 'left')
            ->where('order.assignor', $this->us['user_id'])
            ->whereBetween('order.state', [4, 7])
            ->sum('total_price-payment_price');

        //本月因收款
        $list['monthReceivable'] = db('order', config('database.zong'))
            ->Join('order_aggregate', 'order_aggregate.order_id=order.order_id', 'left')
            ->where('order.assignor', $this->us['user_id'])
            ->whereBetween('finish_time', [$starttime, $enttime])
            ->sum('total_price-payment_price');

        //超期因收款
        $list['overdueReceivable'] = db('order', config('database.zong'))
            ->Join('order_aggregate', 'order_aggregate.order_id=order.order_id', 'left')
            ->join('order_info', 'order_info.order_id=order.order_id', 'left')
            ->where('order.assignor', $this->us['user_id'])
            ->where('order.state', 7)
            ->where('total_price-payment_price>0')
            ->where('(UNIX_TIMESTAMP(NOW())-`finish_time`) >3600*24*3')
            ->sum('total_price-payment_price');


        //待开工
        $list['started'] = db('order', config('database.zong'))
            ->where(function ($quer) {
                $quer->whereNull('order.start_time')->whereOr('order.start_time', 0);
            })
            ->where('order.state', 4)
            ->where('order.assignor', $this->us['user_id'])
            ->count('order_id');
        //施工中
        $list['commencement'] = db('order', config('database.zong'))
            ->where('order.assignor', $this->us['user_id'])
            ->where(function ($quer) {
                $quer->whereNotNull('order.start_time')->where('order.start_time', '<>', 0);
            })
            ->where('order.state', 4)
            ->count('order_id');
        //完工待结算
        $list['beSettled'] = db('order', config('database.zong'))
            ->where('order.assignor', $this->us['user_id'])
            ->where('order.state', 7)
            ->where('if(order.order_agency=1,order.cleared_time IS NULL and order.settlement_time  IS NULL,order.cleared_time  IS NULL )')
            ->count('order_id');

        //本月已结算
        $list['settled'] = db('order', config('database.zong'))
            ->where('order.assignor', $this->us['user_id'])
            ->where('if(order.order_agency=1,order.cleared_time between ' . $starttime . ' and ' . $enttime . ' and order.settlement_time between ' . $starttime . ' and ' . $enttime . ',order.cleared_time between ' . $starttime . ' and ' . $enttime . ')')
//            ->where(function ($quer) use ($starttime, $enttime) {
//                $quer->whereBetween('cleared_time', [$starttime, $enttime])->whereOr('settlement_time', 'between', [$starttime, $enttime]);
//            })
            ->count('order_id');

        r_date($list, 200);
    }

    /*
     * 新订单列表
     */
    public function newList()
    {
        $data      = \request()->get();
        $BeginDate = date('Y-m-01', strtotime(date("Y-m-d", time())));
        $starttime = strtotime($BeginDate);
        $enttime   = strtotime(date('Y-m-d', strtotime("$BeginDate +1 month -1 day")));
        $payment   = db('payment')
            ->field('sum(money) as money,orders_id')
            ->where("IF(payment.weixin=2,payment.success=2,payment.success=1)")
            ->group('orders_id')
            ->buildSql();
        $capital   = db('capital')
            ->field('sum(to_price) as to_price,ordesr_id')
            ->where(['capital.types' => 1, 'capital.enable' => 1])
            ->group('ordesr_id')
            ->buildSql();
        $envelopes = db('envelopes')
            ->field('sum(envelopes.give_money) as give_money,sum(envelopes.expense) as expense,sum(envelopes.purchasing_discount) as purchasing_discount,sum(envelopes.purchasing_expense) as purchasing_expense,ordesr_id,envelopes.gong')
            ->where(['envelopes.type' => 1])
            ->group('ordesr_id')
            ->buildSql();
        $list      = db('order')
            ->join([$envelopes => 'envelopes'], 'envelopes.ordesr_id=order.order_id', 'left')
            ->join([$capital => 'capital'], 'capital.ordesr_id=order.order_id', 'left')
            ->join([$payment => 'payment'], 'payment.orders_id=order.order_id', 'left')
            ->join('contract', 'contract.orders_id=order.order_id', 'left')
            ->join('province', 'order.province_id=province.province_id', 'left')
            ->join('city', 'order.city_id=city.city_id', 'left')
            ->join('county', 'order.county_id=county.county_id', 'left');
        switch ($data['type']) {
            case 1:
//                $list->where('(order.created_time is null or order.created_time=0)' or ('order.settlement_time is null or order.settlement_time=0'))
//                $list->order('contract.con_time desc');
                break;
            case 2:
                $list->whereBetween('finish_time', [$starttime, $enttime]);
                break;
            case 3:
                $list->where('(UNIX_TIMESTAMP(NOW())-`finish_time`) >3600*24*3')
                    ->where('order.state', 7)
                    ->where('ifnull((capital.to_price + (envelopes.expense-envelopes.give_money-envelopes.purchasing_discount+envelopes.purchasing_expense)),0)-ifnull(money,0)>0');
                break;
            case 4:
                $list->where(function ($quer) {
                    $quer->whereNull('order.start_time')->whereOr('order.start_time', 0);
                })->where('order.state', 4);


                break;
            case 5:
                $list->where(function ($quer) {
                    $quer->whereNotNull('order.start_time')->where('order.start_time', '<>', 0);
                })
                    ->where('order.state', 4);

                break;
            case 6:
                $list->where('order.state', 7)
                    ->where('if(order.order_agency=1,order.cleared_time IS NULL and order.settlement_time  IS NULL,order.cleared_time  IS NULL )');

                break;

            default:
//                $list->where(function ($quer) use ($starttime, $enttime) {
//                    $quer->whereBetween('order.cleared_time', [$starttime, $enttime])->whereOr('order.settlement_time', 'between', [$starttime, $enttime]);
//                });
                $list->where('if(order.order_agency=1,order.cleared_time between ' . $starttime . ' and ' . $enttime . ' and order.settlement_time between ' . $starttime . ' and ' . $enttime . ',order.cleared_time between ' . $starttime . ' and ' . $enttime . ')');
        }
        if ($data['sort'] == 1) {
            $xu = "desc";
        } else {
            $xu = "asc";
        }
        switch ($data['status']) {
            case 1:
                $list->order('contract.con_time ' . $xu . '');

                break;
            case 2:
                $list->order('order.start_time ' . $xu . '');
                break;
            default:
                $list->order('order.finish_time ' . $xu . '');
        }
        $listdata = $list->where('order.assignor', $this->us['user_id'])
            ->whereBetween('order.state', [4, 7])
            ->field('ifnull((capital.to_price + (envelopes.expense-envelopes.give_money-envelopes.purchasing_discount+envelopes.purchasing_expense)),0) as signing,ifnull(money,0) as money,gong,FROM_UNIXTIME(contract.con_time,"%Y-%m-%d ") as con_time,if(ifnull(order.start_time,0)=0," ",FROM_UNIXTIME(order.start_time,"%Y-%m-%d ")) as start_time,if(ifnull(order.finish_time,0)=0," ",FROM_UNIXTIME(order.finish_time,"%Y-%m-%d ")) as finish_time,concat(province.province,city.city,county.county,order.addres) as  addres,order.contacts,order.telephone,order.order_id,if(ifnull(order.start_time,0)=0,0,FLOOR(if(ifnull(order.finish_time,0)=0,(UNIX_TIMESTAMP(NOW())-order.start_time),(order.finish_time-order.start_time))/(3600*24))) as nowDay')
            ->page($data['page'], $data['limit'])
            ->select();
        r_date($listdata, 200);
    }

    public function getList()
    {
        $data = \request()->get();


        switch ($data['type']) {
            case 1:
                $list = db('order', config('database.zong'))
                    ->join('order_info', 'order_info.order_id=order.order_id', 'left')
                    ->where('order.assignor', $this->us['user_id'])
                    ->where('FLOOR((UNIX_TIMESTAMP(NOW())-(`start_time`+`gong`))/(3600*24)) >`gong`')
                    ->whereNotNull('start_time')
                    ->where('start_time', '<>', 0)
                    ->where('order.state', 4)
                    ->field('order.order_id,concat(order_info.county,order.addres) as  addres,FLOOR((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(order.start_time,"%Y-%m-%d %H:%i:%s"),INTERVAL gong day)))/(3600*24)) as  dat,concat("超",FLOOR((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(order.start_time,"%Y-%m-%d %H:%i:%s"),INTERVAL gong day)))/(3600*24)),"天") as overtime')->order('dat desc');
                break;
            case 2:

                $payment = db('payment')
                    ->field('sum(money) as money,orders_id')
                    ->where("IF(payment.weixin=2,payment.success=2,payment.success=1)")
                    ->group('orders_id')
                    ->buildSql();
                $list    = db('order')
                    ->join('contract', 'contract.orders_id=order.order_id', 'left')
                    ->join([$payment => 'payment'], 'payment.orders_id=order.order_id', 'left')
                    ->join('county', 'order.county_id=county.county_id', 'left')
                    ->where('order.assignor', $this->us['user_id'])
                    ->where('money', '<>', 0)
                    ->where('order.state', 4)
                    ->where(function ($quer) {
                        $quer->whereNull('order.start_time')->whereOr('order.start_time', 0);
                    })
                    ->field('order.order_id,concat(county.county,order.addres) as  addres,concat("超",FLOOR((UNIX_TIMESTAMP(NOW())-contract.con_time)/(3600*24)),"天") as  overtime,FLOOR((UNIX_TIMESTAMP(NOW())-contract.con_time)/(3600*24)) as  dat')
                    ->order('dat desc');
                break;
            case 3:
                $list = db('order', config('database.zong'))
                    ->join('order_info', 'order_info.order_id=order.order_id', 'left')
                    ->where('order.assignor', $this->us['user_id'])
                    ->Join('order_aggregate', 'order_aggregate.order_id=order.order_id', 'left')
                    ->where('`order_aggregate`.`total_price`<>`order_aggregate`.`payment_price`')
                    ->where('state', 7)
                    ->field('order.order_id,concat(order_info.county,order.addres) as  addres,concat("待收",round((order_aggregate.total_price-order_aggregate.payment_price),2),"元") as overtime,round((order_aggregate.total_price-order_aggregate.payment_price),2) as dat')->order('dat desc');
                break;
            case 4:
                $list = db('order', config('database.zong'))
                    ->join('order_info', 'order_info.order_id=order.order_id', 'left')
                    ->where('order.assignor', $this->us['user_id'])
                    ->where('abs((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(order.start_time,"%Y-%m-%d %H:%i:%s"),INTERVAL gong day)))/(3600*24))< (`gong`* 0.25)')
                    ->where('(UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(order.start_time,"%Y-%m-%d %H:%i:%s"),INTERVAL gong day)))/(3600*24)<0')
                    ->whereNotNull('start_time')
                    ->where('start_time', '<>', 0)
                    ->where('state', 4)
                    ->field('order.order_id,concat(order_info.county,order.addres) as addres,FLOOR((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(order.start_time,"%Y-%m-%d %H:%i:%s"),INTERVAL gong day)))/(3600*24)) as  dat,concat("剩余",abs(FLOOR((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(order.start_time,"%Y-%m-%d %H:%i:%s"),INTERVAL gong day)))/(3600*24))),"天") as overtime')->order('dat desc');
                break;

            default:
                $list = db('order', config('database.zong'))
                    ->join('order_info', 'order_info.order_id=order.order_id', 'left')
                    ->where('order.assignor', $this->us['user_id'])
                    ->where('order.state', 'between', [4, 7])
                    ->Join('order_aggregate', 'order_aggregate.order_id=order.order_id', 'left')
                    ->where('`order_aggregate`.`total_price` * 0.5 >`order_aggregate`.`payment_price`')
                    ->field('order.order_id,concat(order_info.county,order.addres) as  addres,concat("待收",round((order_aggregate.total_price- order_aggregate.payment_price),2),"元") as  overtime,round((order_aggregate.total_price- order_aggregate.payment_price),2) as  dat')->order('dat desc');


        }
        if (isset($data['more']) && $data['more'] == 1) {
            $getList['list'] = $list->page(0, 3)->select();
        } else {
            $getList['list'] = $list->select();
        }


        r_date($getList, 200);
    }

    public function Ranking($type, $mode = 1)
    {

        $BeginDate = date('Y-m-01', strtotime(date("Y-m-d", time())));
        $starttime = strtotime($BeginDate);
        $enttime   = strtotime(date('Y-m-d', strtotime("$BeginDate +1 month -1 day")));
        $userList  = \db('user')->where('user.show_ranking', 1)->where('user.status', 0)->field('username,avatar,user_id')->select();
//        $userList = \db('user')->where('user.status', 0)->field('username,avatar,user_id')->select();
        $user = array_column($userList, 'user_id');

        switch ($type) {
            case 1:
                $list = $this->achievement($starttime, $enttime, $user);
                break;
            case 2:
                $list = $this->Conversion($starttime, $enttime, $user);
                break;
            case 3:
                $list = $this->Transfe($starttime, $enttime, $user);
                break;
            default:
                $list = $this->profit($starttime, $enttime, $user);
                break;
        }
        $userDataList = [];

        foreach ($userList as $k => $item) {

            $userDataList[$k]['username'] = $item['username'];
            $userDataList[$k]['value']    = 0;
            $userDataList[$k]['avatar']   = $item['avatar'];
            $userDataList[$k]['user_id']  = $item['user_id'];
        }

        foreach ($userDataList as $key => $valueA) {
            $id = $valueA['user_id'];
            foreach ($list as $keys => $valueB) {
                if ($id == $valueB['user_id']) {
                    unset($userDataList[$key]);
                }
            }
        }


        $list = array_merge($userDataList, $list);


        $last_data = array_column($list, 'value');
        array_multisort($last_data, SORT_DESC, $list);
        $dataList = [];


        foreach ($list as $k => $item) {
            $dataList[$k]['xu']       = $k + 1;
            $dataList[$k]['username'] = $item['username'];
            $dataList[$k]['value']    = $item['value'];
            $dataList[$k]['avatar']   = $item['avatar'];
            $dataList[$k]['user_id']  = $item['user_id'];

        }


        $count = count($dataList) * 0.1;

        $count = ceil($count);

        $lsit = [];
        for ($i = 1; $i <= $count; $i++) {
            $lsit[] = $dataList[count($dataList) - $i];
        }

        $last_datas = array_column($lsit, 'xu');
        array_multisort($last_datas, SORT_ASC, $lsit);
        if ($mode == 1) {
            r_date(['info' => "\r \r \r \r \r排名数据截止时间：" . date('Y-m-d H:i:s', time()) . " \n 业绩和转化率排名计入末位淘汰,复转率和利润暂不计算", 'list' => $dataList, 'lastList' => $lsit], 200);
        } else {
            return $dataList;
        }


    }

    /*
     * 业绩
     */
    public function achievement($starttime, $enttime, $user)
    {

        $capital   = db('capital')
            ->field('sum(to_price) as to_price,ordesr_id')
            ->where(['capital.types' => 1, 'capital.enable' => 1])
            ->group('ordesr_id')
            ->buildSql();
        $envelopes = db('envelopes')
            ->field('sum(envelopes.give_money) as give_money,sum(envelopes.expense) as expense,sum(envelopes.purchasing_discount) as purchasing_discount,sum(envelopes.purchasing_expense) as purchasing_expense,ordesr_id')
            ->where(['envelopes.type' => 1])
            ->group('ordesr_id')
            ->buildSql();
        $list      = db('order')
            ->join('user', 'order.assignor=user.user_id', 'left')
            ->join('contract', 'order.order_id=contract.orders_id', 'left')
            ->join([$envelopes => 'envelopes'], 'envelopes.ordesr_id=order.order_id', 'left')
            ->join([$capital => 'capital'], 'capital.ordesr_id=order.order_id', 'left')
            ->whereBetween('contract.con_time', [$starttime, $enttime])
            ->whereBetween('order.state', [4, 7])
            ->whereIn('user.user_id', $user)
            ->group('user.user_id')
            ->field('sum(capital.to_price + (envelopes.expense-envelopes.give_money-envelopes.purchasing_discount+envelopes.purchasing_expense)) as signing,user.user_id,user.username,user.avatar,user.user_id')
            ->select();


        foreach ($list as $k => $item) {
            $zeng               = array_sum(array_column((new Financial)->zeng($starttime, $enttime, $item['user_id']), 'turnover'));
            $jian               = array_sum(array_column((new Financial)->jian($starttime, $enttime, $item['user_id']), 'turnover'));
            $tui                = array_sum(array_column((new Financial)->tui($starttime, $enttime, $item['user_id']), 'turnover'));
            $list[$k]['value']  = sprintf('%.2f', $item['signing'] + $zeng - $jian - $tui);
            $list[$k]['avatar'] = $item['avatar'];
        }

        return $list;
    }

    /*
     * 转化率排名
     */
    public function Conversion($start_timestamp, $end_timestamp, $user)
    {


        $_list = db('order')
            ->join('user user', 'user.user_id = order.assignor', 'left')
            ->join('contract contract', 'contract.orders_id = order.order_id', 'left')
            ->where('(order.created_time between ' . $start_timestamp . ' and ' . $end_timestamp . ') or (contract.con_time between ' . $start_timestamp . ' and ' . $end_timestamp . ') or (order.state=9 and contract.con_time<' . $start_timestamp . ' and order.tui_time between ' . $start_timestamp . ' and ' . $end_timestamp . ') ')
            ->whereIn('user.user_id', $user);//排除禁用的号，测试号，店铺禁用的用户

        $list     = $_list->field('order.order_id,order.assignor,order.tui_time,user.username,order.created_time,order.state,order.channel_id,order.channel_details,contract.con_time,user.avatar,user.user_id')->select();
        $_data    = [];
        $dataList = [];
        $assignor = [];
        foreach ($list as $key => $value) {
            $assignor[]                  = $value['assignor'];
            $_data[$value['assignor']][] = $value;
        }
        $i = 0;
        foreach ($_data as $key => $value) {
            $a                              = 0;//派单量
            $b                              = 0;//无效单
            $c                              = 0;//活动单
            $d                              = 0;//成交单
            $e                              = 0;//有效成交
            $f                              = 0;//有效派单
            $g                              = 0;//成交的活动订单
            $h                              = 0;//退单
            $j                              = 0;//历史成交
            $data[$i]['value_no_order']     = [];//无效单
            $data[$i]['value_act_order']    = [];//活动单
            $data[$i]['value_ok_order']     = [];//成交单
            $data[$i]['value_valid_order']  = [];//有效成交
            $data[$i]['value_send_order']   = [];//有效派单
            $data[$i]['value_act_ok_order'] = [];//成交的活动订单
            $data[$i]['value_back_order']   = [];//退单
            $data[$i]['value_old_order']    = [];//以前派单，在本月签约
            foreach ($value as $k => $v) {

                //创建时间在范围内
                if ($v['created_time'] >= $start_timestamp && $v['created_time'] <= $end_timestamp) {
                    $a++;
                    //无效单
                    if (intval($v['state']) == 10) {
                        $b++;
                        $data[$i]['value_no_order'][] = $v['order_id'];
                    }
                    //活动单
                    if (intval($v['channel_details']) == 96 && intval($v['state']) != 10) {
                        $c++;
                        $data[$i]['value_act_order'][] = $v['order_id'];
                    }
                    //成交单
                    if (intval($v['state']) > 3 && intval($v['state']) < 8) {
                        $d++;
                        $data[$i]['value_ok_order'][] = $v['order_id'];
                    }
                    //有效成交
                    if (intval($v['state']) > 3 && intval($v['state']) < 8 && intval($v['channel_details']) != 96) {
                        $e++;
                        $data[$i]['value_valid_order'][] = $v['order_id'];
                    }
                    //有效派单
                    if (intval($v['channel_details']) != 96 && intval($v['state']) != 10) {
                        $f++;
                        $data[$i]['value_send_order'][] = $v['order_id'];
                    }
                    //成交的活动订单
                    if (intval($v['channel_details']) == 96 && intval($v['state']) > 3 && intval($v['state']) < 8) {
                        $g++;
                        $data[$i]['value_act_ok_order'][] = $v['order_id'];
                    }

                }
                //历史成交(派单时间小于开始时间，成交在时间范围内)
                if ($v['created_time'] < $start_timestamp && $v['con_time'] >= $start_timestamp && $v['con_time'] <= $end_timestamp) {
                    //历史成交订单
                    if (intval($v['state']) > 3 && intval($v['state']) < 8) {
                        $j++;
                        $data[$i]['value_old_order'][] = $v['order_id'];
                        $data[$i]['value_ok_order'][]  = $v['order_id'];//成交单+历史成交
                    }
                }
                //退单
                if ($v['con_time'] > 0 && $v['con_time'] < $start_timestamp && $v['state'] == 9 && $v['tui_time'] >= $start_timestamp && $v['tui_time'] <= $end_timestamp) {
                    $h++;
                    $data[$i]['value_back_order'][] = $v['order_id'];
                    $data[$i]['value_ok_order'][]   = $v['order_id'];//成交单+历史成交
                }

                $dataList[$i]['username'] = $v['username'];
                $dataList[$i]['avatar']   = $v['avatar'];
                $dataList[$i]['user_id']  = $v['user_id'];

            }
            //有效成交
            $f                            = $f + $g + $j;//+成交的活动订单+以前派单，在本月签约
            $data[$i]['value_send_order'] = array_merge($data[$i]['value_send_order'], $data[$i]['value_act_ok_order'], $data[$i]['value_old_order']);
            //有效成交
            $d = $d + $j - $h;//+以前派单，在本月签约

            //成交率=（成交量—退单）/有效订单（接单量—无效订单—活动订单+成交的活动订单）
            if ($d > 0 && $f > 0) {
                $rate = round($d / $f * 100, 2);
            } elseif ($d > 0 && $f == 0) {
                $rate = 100;
            } else {
                $rate = 0;
            }

            $dataList[$i]['value'] = $rate;
            $i++;

        }


//        $last_data = array_column($data, 'value');
//        array_multisort($last_data, SORT_ASC, $data);
        //dd($data);
        return $dataList;
    }

    /*
     * 复购转介绍
     */
    public function Transfe($start_timestamp, $end_timestamp, $user)
    {
        $_list = db('order')
            ->join('user user', 'user.user_id = order.assignor', 'left')
            ->join('contract contract', 'contract.orders_id = order.order_id', 'left')
            ->whereBetween('contract.con_time', [$start_timestamp, $end_timestamp])
            ->whereBetween('order.state', [4, 7])
            ->whereIn('user.user_id', $user);//排除禁用的号，测试号，店铺禁用的用户
        $list  = $_list->field('order.order_id,user.username,user.avatar,
     
        round(((
        sum(if(order.channel_id=27 or order.channel_id=28,1,0)) /count(order.order_id)
        )* 100),2) as value,user.user_id')->group('user.user_id')->select();

        return $list;
    }

    /**
     * 利润排行
     */
    public function profit($start, $end, $user_id)
    {
        $capital = db('capital')
            ->field('sum(to_price) as to_price,ordesr_id')
            ->where(['capital.types' => 1, 'capital.enable' => 1])
            ->group('ordesr_id')
            ->buildSql();

        $envelopes = db('envelopes')
            ->field('sum(envelopes.give_money) as give_money,sum(envelopes.expense) as expense,sum(envelopes.purchasing_discount) as purchasing_discount,sum(envelopes.purchasing_expense) as purchasing_expense,ordesr_id')
            ->where(['envelopes.type' => 1])
            ->group('ordesr_id')
            ->buildSql();
        $order     = \db('order')->field('sum(capitals.to_price + (envelopes.expense-envelopes.give_money-envelopes.purchasing_discount+envelopes.purchasing_expense))- (sum(ifNull(reimbursement,0))+sum(ifNull(settlementReimbursement,0)) +sum(ifNull(material_usage,0)) +sum(if(order.notcost_time=null or order.notcost_time=0,1,0))*120 + sum(ifNull(capitalPersonal,0))) as value,user.username,user.avatar,user.user_id')
            ->join('user', 'user.user_id=order.assignor', 'left')
            ->join([\db('reimbursement')->field('sum(reimbursement.money)as reimbursement,reimbursement.order_id')->where(['reimbursement.classification' => ['<>', 4], 'reimbursement.status' => 1])->group('reimbursement.order_id')->buildSql() => 'reimbursement'], 'reimbursement.order_id=order.order_id  and order.cleared_time between ' . $start . ' and ' . $end . '', 'left')
            ->join([\db('reimbursement')->field('sum(reimbursement.money)as settlementReimbursement,reimbursement.order_id')->where(['reimbursement.classification' => 4, 'reimbursement.status' => 1])->group('reimbursement.order_id')->buildSql() => 'reimbursementSettlement'], 'reimbursementSettlement.order_id=order.order_id and order.settlement_time between ' . $start . ' and ' . $end . '', 'left')
            ->join([Db::connect(config('database.db2'))->table(config('database.db2')['database'] . '.app_user_order_capital')->field('sum(' . config('database.db2')['database'] . '.app_user_order_capital.personal_price) as capitalPersonal,' . config('database.db2')['database'] . '.app_user_order_capital.order_id')->whereNull('app_user_order_capital.deleted_at')->group(config('database.db2')['database'] . '.app_user_order_capital.order_id')->buildSql() => 'capital'], 'capital.order_id=order.order_id', 'left')
            ->join([\db('material_usage')->field('sum(material_usage.total_price)as material_usage,material_usage.order_id')->where(['material_usage.status' => 1])->group('material_usage.order_id')->buildSql() => 'u'], 'u.order_id=order.order_id', 'left')->whereIn('user.user_id', $user_id)
            ->join([$envelopes => 'envelopes'], 'envelopes.ordesr_id=order.order_id', 'left')
            ->join([$capital => 'capitals'], 'capitals.ordesr_id=order.order_id', 'left');


        $orderList = $order->where(function ($quer) use ($start, $end) {
            $quer->whereBetween('order.cleared_time', [$start, $end])->whereOr('order.settlement_time', 'between', [$start, $end]);
        })->group('user.user_id')->select();
        return $orderList;


    }

    public function approvalType()
    {
        r_date([
            ['title' => '全部', 'type' => 0],
            ['title' => '订单费用报销', 'type' => 1],
            ['title' => "主材费用报销", 'type' => 2],
            ['title' => "返工费用报销", 'type' => 3],
            ['title' => "补充库存", 'type' => 4],
            ['title' => "新材料申请", 'type' => 5],
            ['title' => "材料领用", 'type' => 9],
            ['title' => "材料借调", 'type' => 10],
            ['title' => "返工材料领用", 'type' => 11]], 200);
    }

    /*
     * 审批提醒
     */
    public function approvalReminder()
    {
        $data = \request()->get();
        $list = db('approval_info', config('database.zong'))
            ->join('order_info', 'order_info.order_id=approval_info.order_id', 'left')
            ->join('order', 'order.order_id=order_info.order_id', 'left');
        if (isset($data['type']) && $data['type'] != 0) {
            $list->where('approval_info.type', $data['type']);
        }
        if (isset($data['status'])) {
            $list->where('approval_info.approval_status', $data['status']);
        }
        if (isset($data['examineTime']) && $data['examineTime'] !='') {
            $BeginDate = date('Y-m-01', strtotime($data['examineTime']));
            $starttime = strtotime($BeginDate);
            $enttime   = strtotime(date('Y-m-d', strtotime("$BeginDate +1 month -1 day")));
            $list->whereBetween('approval_info.approval_time',[$starttime,$enttime]);
        }

        $listArray = $list->where('user_id', $this->us['user_id'])->where('user_type', 1)->page($data['page'], $data['limit'])->field('CASE approval_info.type WHEN 1 THEN "订单费用报销"WHEN 2 THEN "主材费用报销"WHEN 3 THEN "返工费用报销"WHEN 4 THEN "补充库存"WHEN 5 THEN "新材料申请" WHEN 9 THEN "材料领用" WHEN 10 THEN  "材料借调" WHEN 11 THEN  "返工材料领用" END AS status,ifnull(concat(order_info.county,order.addres),approval_info.title) as  addres,FROM_UNIXTIME(approval_info.approval_time,"%Y-%m-%d ") as approvalTime,ifnull(order.contacts,approval_info.reject_user) as contacts,ifnull(order.telephone," ") as telephone,approval_info.reject_desc,if(approval_info.read_time=0,0,1) as readTime ,approval_info.order_id,approval_info.id,concat("金额：",approval_info.money,"元") as money,approval_info.type')->order('readTime ASC,approvalTime desc')->select();
        $count     = db('approval_info', config('database.zong'))->where('user_id', $this->us['user_id']);
        if (isset($data['type']) && $data['type'] != 0) {
            $count->where('approval_info.type', $data['type']);
        }
        if (isset($data['examineTime']) && $data['examineTime'] != 0) {
            $BeginDate = date('Y-m-01', strtotime($data['examineTime']));
            $starttime = strtotime($BeginDate);
            $enttime   = strtotime(date('Y-m-d', strtotime("$BeginDate +1 month -1 day")));
            $count->whereBetween('approval_info.approval_time',[$starttime,$enttime]);
        }
        $count  = $count->where('user_type', 1)->field('approval_status')->select();
        $reject = [];
        $adopt  = [];
        foreach ($count as $list) {
            if ($list['approval_status'] == 1) {
                $adopt[] = $list;
            }
            if ($list['approval_status'] == 2) {
                $reject[] = $list;
            }

        }
        $listArrayData['data']   = $listArray;
        $listArrayData['adopt']  = count($adopt);
        $listArrayData['reject'] = count($reject);
        r_date($listArrayData, 200);

    }
    /*
     * 审批提醒
     */
    public function clickApprovalReminder()
    {
        $data = \request()->get();
        db('approval_info', config('database.zong'))->where('id',$data['id'])->update(['read_time'=>time()]);

        r_date(null, 200);

    }


}